package cmp.database;

import java.sql.ResultSet;
import java.util.Vector;

import assem.DBConn;
import util.Debug.ErrorReport;
import cmp.main.Globals;

public class DBinfo {

	static public boolean hasTable(DBConn mDB, String name) throws Exception
	{
		ResultSet rs = mDB.executeQuery("show tables like '" + name + "'");
		boolean retVal = rs.first();
		rs.close();
		return retVal;
	}
	static public boolean tableHasColumn(DBConn mDB, String table, String column) throws Exception
	{
		ResultSet rs = mDB.executeQuery("show columns from " + table + " where field='" + column + "'");
		boolean ret = rs.first();
		rs.close();
		return ret;
	}
	/****************************************************************
	 * XXX called by ViewerFrame on startup - update info fields
	 */
	public DBinfo(DBConn dbc, boolean update) {
		mDB = dbc;
		if (update) {
			new Version(dbc, Globals.VERSION);
			setInfoCol();
		}
		else getInfoCol();
	}
	
	public void setInfoCol() {
		try {
			setASM();
			setMet();
			setTaxa();
			setSeqLibDE();
			setPOGLibDE();
			
			mDB.executeUpdate("update info set " +
					" allASM = " + join(allASM) + "," + 
					" allTaxa= " + join(allTaxa) + "," + 
					" allMethods=" + join(allMethods) + "," + 
					" allSeqLib=" + join(allSeqLib) + "," + 
					" allSeqDE=" + join(allSeqDE) + "," +
					" allPOGLib=" + join(allPOGLib) + "," + 
					" allPOGDE=" + join(allPOGDE) + "," +
					" hasDE=" + allSeqDE.length + "," +
					" hasLib=" + allSeqLib.length 
					);
			setIsPeptide();
		}
		catch(Exception e) {
			ErrorReport.die(e, "Error updating info");
		}
	}
	// XXX the POGLib and POGDE are probably no longer necessary as the lists will be the same as Seq
	public void getInfoCol() {
		try {
			ResultSet rs = mDB.executeQuery(
					"select allASM, allMethods, allTaxa, " +
					"allSeqLib, allPOGLib, allSeqDE, allPOGDE from info");
			if (!rs.next()) ErrorReport.die("Unexplained error when reading columns");
			
			String sASM = rs.getString(1);
			String sMet = rs.getString(2);
			String sTaxa = rs.getString(3);
			String ssLib = rs.getString(4);
			String spLib = rs.getString(5);
			String ssDE = rs.getString(6);
			String spDE = rs.getString(7);
			rs.close();
			
			if (sASM == null || sASM.equals("")) setASM();
			else allASM = sASM.trim().split(" ");
			
			if (sMet == null || sMet.equals("")) setMet();
			else allMethods = sMet.trim().split(" ");
			
			if (sTaxa == null || sTaxa.equals("")) setTaxa();
			else allTaxa = sTaxa.trim().split(" ");
			
			if (ssLib == null || ssLib.equals("") || ssDE == null || ssDE.equals("")) setSeqLibDE();
			else {
				allSeqLib = ssLib.trim().split(" ");
				allSeqDE  = ssDE.trim().split(" ");
				
				for (int i=0; i< allSeqDE.length && hasNegDE==false; i++) {
					String col = Globals.PRE_DE + allSeqDE[i];
					rs = mDB.executeQuery("select UTstr from unitrans where " + 
							 col + " < 0 and " + col +  "!= " + Globals.DEnoVal + " limit 1");
					if (rs.first()) hasNegDE=true;
				}
			}
			if (spLib == null || spLib.equals("") || spDE == null || spDE.equals("")) setPOGLibDE();
			else {
				allPOGLib = spLib.trim().split(" ");
				allPOGDE  = spDE.trim().split(" ");
			}
			
			if (sASM==null || sMet==null || sTaxa==null || ssLib==null || ssDE==null 
					|| spLib==null || spDE==null) {
				mDB.executeUpdate("update info set " +
						" allASM = " + join(allASM) + "," + 
						" allTaxa= " + join(allTaxa) + "," + 
						" allMethods=" + join(allMethods) + "," + 
						" allSeqLib=" + join(allSeqLib) + "," + 
						" allSeqDE=" + join(allSeqDE) + "," +
						" allPOGLib=" + join(allPOGLib) + "," + 
						" allPOGDE=" + join(allPOGDE) + "," +
						" hasDE=" + allSeqDE.length + "," +
						" hasLib=" + allSeqLib.length 
						);
			}
			setIsPeptide();
		}
		catch(Exception e) {
			ErrorReport.die(e, "Error loading columns");
		}
	}
	private static String join(String [] s) {
		if (s==null) return "'" + "" + "'";
		StringBuffer buffer = new StringBuffer();
		
		for (int i=0; i< s.length; i++) buffer.append(s[i] + " ");
		
		return "'" + buffer.toString() + "'";
	}
	private void setASM() {
		try {
			ResultSet rs = mDB.executeQuery("select ASMstr from assembly");
			Vector <String> col = new Vector <String> ();
			while (rs.next()) {
				col.add(rs.getString(1));
			}
			allASM = new String [col.size()];
			for(int x=0; x<allASM.length; x++) {
				allASM[x] = col.get(x);
			}
			col.clear(); rs.close();
		}
		catch (Exception e){
			ErrorReport.prtReport(e, "Error getting methods");
		}	
	}
	private void setMet() {
		try {
			ResultSet rs = mDB.executeQuery("select prefix from pog_method");
			Vector <String> col = new Vector <String> ();
			while (rs.next()) {
				col.add(rs.getString(1));
			}
			allMethods = new String [col.size()];
			for(int x=0; x<allMethods.length; x++) {
				allMethods[x] = col.get(x);
			}
			col.clear(); rs.close();
		}
		catch (Exception e){
			ErrorReport.prtReport(e, "Error getting methods");
		}
	}
	private void setTaxa() {
		try {
			ResultSet rs = mDB.executeQuery("SELECT taxa FROM pog_groups GROUP BY taxa ORDER BY taxa ASC");
			
			Vector<String> results = new Vector<String> ();
			while(rs.next()) {
				String val = rs.getString(1);
				if(val != null && val.length() > 0 && !results.contains(val))
					results.add(val);
			}
			allTaxa = new String [results.size()];
			for(int x=0; x<allTaxa.length; x++) {
				allTaxa[x] = results.get(x);
			}
			results.clear();
			rs.close();
		}
		catch(Exception e) {
			ErrorReport.prtReport(e, "Error loading taxa");
		}
		catch(Error e) {
			ErrorReport.die(e, "Fatal error loading taxa");
		}
	}
	
	private void setSeqLibDE() {
		try {		
			Vector <String> colR = new Vector <String> ();
			Vector <String> colD = new Vector <String> ();
			int l1 = Globals.PRE_LIB.length(); int l2 = Globals.PRE_DE.length();
			
			ResultSet rs = mDB.executeQuery("Show columns from unitrans");
			while (rs.next()) {
				String colName = rs.getString(1);
				if(colName.startsWith(Globals.PRE_LIB)) colR.add(colName.substring(l1));
				else if(colName.startsWith(Globals.PRE_DE)) colD.add(colName.substring(l2));
			}	
			allSeqLib = new String[colR.size()];
			for(int x=0; x<allSeqLib.length; x++) allSeqLib[x] = colR.get(x);
			
			allSeqDE = new String[colD.size()];
			for(int x=0; x<allSeqDE.length; x++) allSeqDE[x] = colD.get(x);

			for (int i=0; i< allSeqDE.length && hasNegDE==false; i++) {
				String col = Globals.PRE_DE + allSeqDE[i];
				rs = mDB.executeQuery("select UTstr from unitrans where " + 
						 col + " < 0 and " + col +  "!= " + Globals.DEnoVal + " limit 1");
				if (rs.first()) hasNegDE=true;
			}
			
			colR.clear(); colD.clear();
			rs.close();			
		}
		catch(Exception e) {
			ErrorReport.prtReport(e, "Error loading library names");
		}
		catch(Error e) {
			ErrorReport.die(e, "Fatal error loading library names");
		}
	}
	
	private void setPOGLibDE() {
		try {
			Vector <String> colR = new Vector <String> ();
			Vector <String> colD = new Vector <String> ();
			int l1 = Globals.PRE_LIBCNT.length(); 
			int l2 = Globals.PRE_DECNT.length();
			
			ResultSet rs = mDB.executeQuery("Show columns from pog_groups");
			while (rs.next()) {
				String colName = rs.getString(1);
				if(colName.startsWith(Globals.PRE_LIBCNT)) colR.add(colName.substring(l1));
				else if(colName.startsWith(Globals.PRE_DECNT)) colD.add(colName.substring(l2));
			}
			allPOGLib = new String[colR.size()];
			for(int x=0; x<allPOGLib.length; x++) allPOGLib[x] = colR.get(x);
			
			allPOGDE = new String[colD.size()];
			for(int x=0; x<allPOGDE.length; x++) allPOGDE[x] = colD.get(x);

			colR.clear(); colD.clear();
			
			rs.close();			
		}
		catch(Exception e) {
			ErrorReport.prtReport(e, "Error loading library names");
		}
		catch(Error e) {
			ErrorReport.die(e, "Fatal error loading library names");
		}
	}
	private void setIsPeptide() {
		try {
			ResultSet rs = mDB.executeQuery("Select isPep from assembly");
			int cntPep=0, cntAll=0;
			while (rs.next()) {
				cntAll++;
				int ip = rs.getInt(1);
				if (ip == 1) cntPep++;
			}
			if (cntPep==cntAll) isPeptide=true;
		}
		catch(Exception e) {
			ErrorReport.prtReport(e, "Error setting isPeptide");
		}
	}
	public String [] getTaxa() { return allTaxa; }
	public String [] getSeqDE() { return allSeqDE; }
	public String [] getSeqLib() { return allSeqLib; }
	public String [] getPOGDE() { return allPOGDE; }
	public String [] getPOGLib() { return allPOGLib; }
	public String [] getMethods() { return allMethods;}
	public String [] getASM() { return allASM;}
	public int getASMnum() { return allASM.length;}
	public boolean isPeptide() { return isPeptide;}
	
	public String getSeqDESQL() { 
		if (allSeqDE==null || allSeqDE.length==0) return "";
		String sql = Globals.PRE_DE + allSeqDE[0];
		for (int i=1; i<allSeqDE.length; i++) sql += "," + Globals.PRE_DE + allSeqDE[i];
		return sql;
	}
	public String getSeqLibSQL() { 
		if (allSeqLib==null || allSeqLib.length==0) return "";
		String sql = Globals.PRE_LIB + allSeqLib[0];
		for (int i=1; i<allSeqLib.length; i++) sql += "," + Globals.PRE_LIB + allSeqLib[i];
		return sql;
	}
	public String getPOGDESQL() { 
		if (allPOGDE==null || allPOGDE.length==0) return "";
		String sql = Globals.PRE_DECNT + allPOGDE[0];
		for (int i=1; i<allPOGDE.length; i++) sql += "," + Globals.PRE_DECNT + allPOGDE[i];
		return sql;
	}
	
	public String getPOGLibSQL() { 
		if (allPOGLib==null || allPOGLib.length==0) return "";
		String sql = Globals.PRE_LIBCNT + allPOGLib[0];
		for (int i=1; i<allPOGLib.length; i++) sql += "," + allPOGLib[i];
		return sql;
	}
	
	public boolean hasNegDE() {
		return hasNegDE;
	}
	
	private String [] allTaxa=null;
	private String [] allSeqDE=null;
	private String [] allSeqLib=null;
	private String [] allPOGDE=null;
	private String [] allPOGLib=null;
	private String [] allMethods=null;
	private String [] allASM=null;
	private boolean isPeptide=false;
	private boolean hasNegDE=false;
	
	DBConn mDB;
}
